package com.itheima.cs.dao.dbd;

import com.itheima.cs.entity.dbd.WarehouseInfo;
import com.itheima.cs.help.DbOpenHelper;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class WarehouseDao extends DbOpenHelper {

    public List<WarehouseInfo> searchWarehouses(String keyword, String stockProperty) {
        List<WarehouseInfo> list = new ArrayList<>();
        try {
            getConnection();
            String sql = "SELECT FNUMBER, FNAME, FWCMPERS, FSTOCKPROPERTY " +
                    "FROM [dbo].[v_t_bd_stock] " +
                    "WHERE FSTOCKPROPERTY = ? " +
                    "AND (FNUMBER LIKE ? OR FNAME LIKE ?)";
            pStmt = conn.prepareStatement(sql);
            pStmt.setString(1, stockProperty);
            String searchParam = "%" + keyword + "%";
            pStmt.setString(2, searchParam);
            pStmt.setString(3, searchParam);

            rs = pStmt.executeQuery();
            while (rs.next()) {
                WarehouseInfo info = new WarehouseInfo(
                        rs.getString("FNUMBER"),
                        rs.getString("FNAME"),
                        rs.getString("FSTOCKPROPERTY"),
                        rs.getString("FWCMPERS")
                );
                list.add(info);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            closeAll();
        }
        return list;
    }

    public WarehouseInfo getByNumber(String number, String stockProperty) {
        WarehouseInfo info = null;
        try {
            getConnection();
            String sql = "SELECT FNUMBER, FNAME, FWCMPERS FROM [dbo].[v_t_bd_stock] " +
                    "WHERE FNUMBER = ? AND FSTOCKPROPERTY = ?";
            pStmt = conn.prepareStatement(sql);
            pStmt.setString(1, number);
            pStmt.setString(2, stockProperty);

            rs = pStmt.executeQuery();
            if (rs.next()) {
                info = new WarehouseInfo(
                        rs.getString("FNUMBER"),
                        rs.getString("FNAME"),
                        stockProperty,
                        rs.getString("FWCMPERS")
                );
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            closeAll();
        }
        return info;
    }

    public List<WarehouseInfo> searchOutsourcingWarehouses(String keyword) {
        List<WarehouseInfo> list = new ArrayList<>();
        try {
            getConnection();
            String sql = "SELECT FNUMBER, FNAME, FWCMPERS, FSTOCKPROPERTY " +
                    "FROM [dbo].[v_t_bd_stock] " +
                    "WHERE FSTOCKPROPERTY = '3' " +
                    "AND (FNUMBER LIKE ? OR FNAME LIKE ?)";
            pStmt = conn.prepareStatement(sql);
            String searchParam = "%" + keyword + "%";
            pStmt.setString(1, searchParam);
            pStmt.setString(2, searchParam);

            rs = pStmt.executeQuery();
            while (rs.next()) {
                WarehouseInfo info = new WarehouseInfo(
                        rs.getString("FNUMBER"),
                        rs.getString("FNAME"),
                        rs.getString("FSTOCKPROPERTY"),
                        rs.getString("FWCMPERS")
                );
                list.add(info);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            closeAll();
        }
        return list;
    }

}